import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    db='gamedb',  # 制定操作哪一个数据库
    charset='utf8'  # 制定操作的字符集
)
cursor = conn.cursor()

# 定义插入数据的sql命令
insert_sql = 'INSERT INTO student VALUES (%s, %s)'

# 1.1 插入一条数据到表 student
# cursor.execute(insert_sql,(1,'庞丽静'))
# conn.commit()  # 把SQL 语句提交到服务器


# 1.2 插入多行数据, 用 executemany 方法 第二个参数是 列表
# cursor.executemany(insert_sql,[(2,'静静'),(3,'丫丫'),(4,'静丫丫'),(5,'漂亮姐')])
# conn.commit()  # 把SQL 语句提交到服务器


# 2. 查询数数据(定义查询语句并放到碗里)
select_sql = 'SELECT stu_num, name FROM student'
cursor.execute(select_sql)

# 2.1 取出一行数据
# result1 = cursor.fetchone()
# print(result1)

# 2.2 取出2行数据
# result2 = cursor.fetchmany(2)
# print(result2)


# 2.3 取出剩余的全部数据
# result3 = cursor.fetchall()
# print(result3)


# 3. 修改
# update_sql = 'UPDATE student SET name=%s WHERE stu_num=%s'
# cursor.execute(update_sql, ('大宝贝',2))
# conn.commit()  # 提交

# 4. 删除
delete_sql = 'DELETE FROM student WHERE stu_num=%s'
r = cursor.execute(delete_sql, (4,))
conn.commit()

# 断开连接
cursor.close()  # 关闭游标
conn.close()  # 关闭连接
